/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package CONTABILIDAD_CONTROLADOR;

import Conexion.Conexion;
import RRHH_Modelo.RHH_USUARIO;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author equipo_4
 */
@WebServlet(name = "CON_VISTA_LIBRO_COMPRAS_CONTROLLER", urlPatterns = {"/CON_VISTA_LIBRO_COMPRAS_CONTROLLER"})
public class CON_VISTA_LIBRO_COMPRAS_CONTROLLER extends HttpServlet {

    /**
     * Processes requests for both HTTP
     * <code>GET</code> and
     * <code>POST</code> methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
          
        } finally {            
            out.close();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            RHH_USUARIO usuario = ((RHH_USUARIO)request.getSession().getAttribute("usr"));
            Conexion con = new Conexion(usuario.getUSUARIO(), usuario.getPASSWORD());
                
            String proceso = request.getParameter("proceso");
            String data="";
            
            switch(proceso)
            {
                case "concc":
                    data=concc(request,con);
                    break;              
                case "busqueda":
                    data=busqueda(request,con);
                    break;              
            }
            
            response.setCharacterEncoding("UTF-8");
            response.setContentType("text/plain");
            response.getWriter().write(data);
            con.Close();
        } catch (SQLException ex) {
            Logger.getLogger(CON_VISTA_LIBRO_COMPRAS_CONTROLLER.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

   
    private String concc(HttpServletRequest request, Conexion con) throws SQLException {                        
        
        String tipo = request.getParameter("tipo");
        String consulta="";
        if(tipo.equals("1"))
        {
            String mes = request.getParameter("mes");
            String ano = request.getParameter("ano");
            consulta = "select * from BROKER.CON_VISTA_LIBRO_COMPRAS where CODIGO_COMPROBANTE like('%-"+mes+"-"+ano+"')";
        }
        else
        {
            String de = request.getParameter("de");
            String hasta = request.getParameter("hasta");
            consulta = "select * from BROKER.CON_VISTA_LIBRO_COMPRAS where FECHA between to_date('"+de+"','mm/dd/yyyy') and to_date('"+hasta+"','mm/dd/yyyy')";
        }
        
        ResultSet rs = con.EjecutarConsulta(consulta);
        
        String data="<thead>";
        data+="<th>NRO FACTURA</th>";
        data+="<th>FECHA</th>";
        data+="<th>COMPROBANTE</th>";
        data+="<th>NRO AUTORIZACION</th>";
        data+="<th>COD CONTROL</th>";
        data+="<th>TOTAL FACTURA</th>";
        data+="<th>ICE</th>";
        data+="<th>EXCENTO</th>";
        data+="<th>RETENCION</th>";        
        data+="<th>TIPO</th>";
        data+="<th>TIPO CAMBIO</th>";
        data+="<th>DESC CUENTA AFECTADA</th>";
        data+="<th>COD CUENTA AFECTADA</th>";
        data+="<th>DESC CENTRO AFECTADO</th>";
        data+="<th>COD CENTRO AFECTADO</th>";
        data+="<th>DESC RETENCION AFECTADA</th>";
        data+="<th>COD RETENCION AFECTADA</th>";
        data+="</thead>";                
        data+="<tbody>";
        while(rs.next())
        {
        data+="<tr>";
        data+="<td>"+rs.getString("NUMERO_FACTURA")+"</td>";
        data+="<td>"+rs.getDate("FECHA")+"</td>";
        data+="<td>"+rs.getString("CODIGO_COMPROBANTE")+"</td>";
        data+="<td>"+rs.getString("NRO_AUTORIZACION")+"</td>";
        data+="<td>"+rs.getString("CODIGO_CONTROL")+"</td>";
        data+="<td>"+rs.getString("TOTAL_FACTURA")+"</td>";
        data+="<td>"+rs.getString("ICE")+"</td>";
        data+="<td>"+rs.getString("EXCENTO")+"</td>";
        data+="<td>"+rs.getString("RETENCION")+"</td>";        
        data+="<td>"+rs.getString("TIPO")+"</td>";
        data+="<td>"+rs.getString("TIPO_CAMBIO")+"</td>";
        data+="<td>"+rs.getString("CUENTA_AFECTADA")+"</td>";
        data+="<td>"+rs.getString("CODIGO_AFECTADA")+"</td>";
        String cc =rs.getString("CODIGO_CXC");
        String ccd =rs.getString("DESCRIPCION_CXC");
        if(ccd==null)
            data+="<td></td>";
        else
            data+="<td>"+ccd+"</td>";
        
        if(cc==null)
            data+="<td></td>";
        else
            data+="<td>"+cc+"</td>";
        
        data+="<td>"+rs.getString("DESCRIPCION_RETENCION")+"</td>";
        data+="<td>"+rs.getString("CODIGO_RETENCION")+"</td>";
        data+="</tr>";    
        }
        data+="</tbody>";
        return data;
    }
    private String busqueda(HttpServletRequest request, Conexion con) throws SQLException {                        
        
        String nroFactura = request.getParameter("nroFactura");
        String fecha = request.getParameter("fecha");
        String codComprobante = request.getParameter("codComprobante");
        String nroAutorizacion = request.getParameter("nroAutorizacion");
        String codControl = request.getParameter("codControl");
        String totalFactura = request.getParameter("totalFactura");
        String descCuenta = request.getParameter("descCuenta");
        String MontoNeto = request.getParameter("MontoNeto");
        String codCuenta = request.getParameter("codCuenta");
        String desccentro = request.getParameter("desccentro");
        String codCentro = request.getParameter("codCentro");
        String consulta = "select * from BROKER.CON_VISTA_LIBRO_COMPRAS "
                + "where  (TO_CHAR(TO_NUMBER(NUMERO_FACTURA))) like('"+nroFactura+"%')"
                + " and CODIGO_COMPROBANTE LIKE('"+codComprobante+"%') and CODIGO_CONTROL LIKE('"+codControl+"%')"
                + " and  upper(CODIGO_CXC) like(upper('"+codCentro+"%')) and  upper(DESCRIPCION_CXC) like(upper('"+desccentro+"%'))"
                + " and  upper(CODIGO_AFECTADA) like(upper('"+codCuenta+"%')) and  upper(CUENTA_AFECTADA) like(upper('"+descCuenta+"%'))";
        
        if(nroAutorizacion.length()>0){
            consulta+=" and NRO_AUTORIZACION ="+nroAutorizacion;
        }
        if(nroFactura.length()>0){
            consulta+=" and NUMERO_FACTURA="+nroFactura;
        }
        if(totalFactura.length()>0){
            consulta+=" and TOTAL_FACTURA="+totalFactura;
        }
        if(MontoNeto.length()>0){
            consulta+=" and MONTO_NETO="+MontoNeto;
        }
        if(fecha.length()>0){
            consulta+=" and to_date(FECHA,'dd/mm/yyyy') like to_date('"+fecha+"','dd/mm/yyyy')";
        }
        ResultSet rs = con.EjecutarConsulta(consulta);
        
        String data="<thead>";
        data+="<th>NRO FACTURA</th>";
        data+="<th>FECHA</th>";
        data+="<th>COMPROBANTE</th>";
        data+="<th>NRO AUTORIZACION</th>";
        data+="<th>COD CONTROL</th>";
        data+="<th>TOTAL FACTURA</th>";
        data+="<th>ICE</th>";
        data+="<th>EXCENTO</th>";
        data+="<th>RETENCION</th>";        
        data+="<th>TIPO</th>";
        data+="<th>TIPO CAMBIO</th>";
        data+="<th>DESC CUENTA AFECTADA</th>";
        data+="<th>COD CUENTA AFECTADA</th>";
        data+="<th>DESC CENTRO AFECTADO</th>";
        data+="<th>COD CENTRO AFECTADO</th>";
        data+="<th>DESC RETENCION AFECTADA</th>";
        data+="<th>COD RETENCION AFECTADA</th>";
        data+="</thead>";                
        data+="<tbody>";
        while(rs.next())
        {
        data+="<tr>";
        data+="<td>"+rs.getString("NUMERO_FACTURA")+"</td>";
        data+="<td>"+rs.getDate("FECHA")+"</td>";
        data+="<td>"+rs.getString("CODIGO_COMPROBANTE")+"</td>";
        data+="<td>"+rs.getString("NRO_AUTORIZACION")+"</td>";
        data+="<td>"+rs.getString("CODIGO_CONTROL")+"</td>";
        data+="<td>"+rs.getString("TOTAL_FACTURA")+"</td>";
        data+="<td>"+rs.getString("ICE")+"</td>";
        data+="<td>"+rs.getString("EXCENTO")+"</td>";
        data+="<td>"+rs.getString("RETENCION")+"</td>";        
        data+="<td>"+rs.getString("TIPO")+"</td>";
        data+="<td>"+rs.getString("TIPO_CAMBIO")+"</td>";
        data+="<td>"+rs.getString("CUENTA_AFECTADA")+"</td>";
        data+="<td>"+rs.getString("CODIGO_AFECTADA")+"</td>";
        String cc =rs.getString("CODIGO_CXC");
        String ccd =rs.getString("DESCRIPCION_CXC");
        if(ccd==null)
            data+="<td></td>";
        else
            data+="<td>"+ccd+"</td>";
        
        if(cc==null)
            data+="<td></td>";
        else
            data+="<td>"+cc+"</td>";
        
        data+="<td>"+rs.getString("DESCRIPCION_RETENCION")+"</td>";
        data+="<td>"+rs.getString("CODIGO_RETENCION")+"</td>";
        data+="</tr>";    
        }
        data+="</tbody>";
        return data;
    }
    
}
